import java.io.*;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import org.json.*;

public final class fnsiDownloader {
    static private String SQLITE3_CONNECTION = null;
    static private String VALIDATOR_DATA_PATH = null;
    static private String VALIDATOR_URL = null;
    static private String FNSI_USERKEY = null;
    static private final int FNSI_PORTION = 2000;

    static private Connection conn = null;
    static private HashSet<String> downloaded = new HashSet<String>();
    static private final StringBuilder builder = new StringBuilder(50000);

    static private void sendServletCommand(final String command) throws IOException {
        if (VALIDATOR_URL != null) {
            URL url = new URL(VALIDATOR_URL + command);
            HttpURLConnection conn = (HttpURLConnection) url.openConnection();
            conn.setConnectTimeout(3000);
            conn.setRequestProperty("Content-Type", "text/plain; charset=UTF-8");
            conn.setRequestProperty("Accept", "text/plain");
            int status = conn.getResponseCode();
            BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream(), "utf-8"));
            String output;
            builder.setLength(0);
            while ((output = in.readLine()) != null) {
                builder.append(output);
            }
            System.out.println(VALIDATOR_URL + command + " Reponse='" + builder.toString() + "' ResponseCode-" + status);
        }
    }

    static private JSONObject getFNSIJSON(URL url, boolean needlog) throws IOException {
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        conn.setConnectTimeout(3000);
        conn.setRequestProperty("Content-Type", "application/json; charset=UTF-8");
        conn.setRequestProperty("Accept", "application/json");
        BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream(), "utf-8"), 300000);
        String output;
        builder.setLength(0);
        while ((output = in.readLine()) != null) {
            builder.append(output);
        }
        JSONObject ret = new JSONObject(builder.toString());
        if (needlog || !"OK".equals(ret.get("result"))) {
            System.out.println(url + " Reponse='" + ret.get("result") + "'" + (!ret.isNull("resultText") ? ", '" + ret.get("resultText") + "'": ""));
        }
        return ret;
    }

    static private void loadFNSIlist_json() throws IOException, SQLException {
        JSONTokener tokener = new JSONTokener(
                new FileReader(VALIDATOR_DATA_PATH + "FNSIlist.json", StandardCharsets.UTF_8));
        JSONArray FNSIlist = (JSONArray) new JSONArray(tokener);

        HashMap<String, String[]> prev = new HashMap<String, String[]>();
        PreparedStatement st = conn.prepareStatement(String.join(" ",
                "select codeSystem,",
                "ifnull(keyField,'null')||ifnull(valueField,'null')||ifnull(REKV, 'null'),",
                "cast(load as text)||cast(ifnull(validateRefName,1) as text)||cast(ifnull(validateValue,1) as text)",
                "from ref_setting"));
        ResultSet rs = st.executeQuery();
        while (rs.next()) {
            prev.put(rs.getString(1), new String[] { rs.getString(2), rs.getString(3) });
        }
        rs.close();
        st.close();

        HashMap<String, Integer> params = new HashMap<String, Integer>();
        params.put("CODESYSTEM", 0);
        params.put("LOAD", 1);
        params.put("VALIDATE_REFNAME", 2);
        params.put("KEY", 3);
        params.put("VALUE", 4);
        params.put("VALIDATE_VALUE", 5);
        params.put("REKV", 6);
        params.put("COMMENT", 7);
        Object[] defaults = new Object[] { null, 1, 1, null, null, 1, null, null };

        PreparedStatement st1 = conn.prepareStatement("delete from ref_setting where codeSystem=?");
        PreparedStatement st2 = conn
                .prepareStatement("INSERT INTO ref_setting VALUES (" + "?,".repeat(params.size() - 1) + "?" + ")");
        PreparedStatement st3 = conn
                .prepareStatement("select codeSystem, version, rowsCount from fnsi_ref where codeSystem=?");
        PreparedStatement st4 = conn
                .prepareStatement("delete from fnsi_ref where codeSystem=?");
        for (Object _psObject : FNSIlist) {
            JSONObject psObject = (JSONObject) _psObject;
            Object[] pa = defaults.clone();

            for (String p : params.keySet()) {
                if (psObject.toMap().containsKey(p)) {
                    int i1 = params.get(p);
                    if (psObject.get(p) instanceof JSONArray) {
                        StringJoiner sj = new StringJoiner(",");
                        for (Object s : psObject.getJSONArray(p)) {
                            sj.add((String) s);
                        }
                        pa[i1] = sj.toString();
                    } else {
                        pa[i1] = psObject.get(p);
                    }
                }
            }

            String rekv = ((pa[3] != null) ? pa[3].toString() : "null") + ((pa[4] != null) ? pa[4].toString() : "null")
                    + ((pa[6] != null) ? pa[6].toString() : "null");
            String settings = pa[1].toString() + ((pa[2] != null) ? pa[2].toString() : 1)
                    + ((pa[5] != null) ? pa[5].toString() : 1);

            if (prev.get(pa[0]) == null || !prev.get(pa[0])[0].equals(rekv) || !prev.get(pa[0])[1].equals(settings)) {
                st1.setString(1, (String) pa[0]);
                st1.executeUpdate();

                for (int i = 0; i < pa.length; i++) {
                    st2.setObject(i + 1, pa[i]);
                }
                st2.executeUpdate();

                if (prev.get(pa[0]) == null || !prev.get(pa[0])[0].equals(rekv)) {
                    // посылаем команду на перезагрузку всех данных справочника в сервис валидации
                    // СЭМД
                    sendServletCommand("/reloadRefData/" + pa[0]);

                    // удаляем файлы справочников
                    st3.setObject(1, pa[0]);
                    ResultSet rs3 = st3.executeQuery();
                    if (rs3.next()) {
                        File avl_file = new File(
                                VALIDATOR_DATA_PATH + "/avl/" + rs3.getString(1) + "-" + rs3.getString(2) + ".avl");
                        avl_file.delete();
                    }
                    rs3.close();

                    // удаляем сведения справочниках из БД, это приведет к перезагрузке справочников из ФНСИ
                    st4.setObject(1, pa[0]);
                    st4.executeUpdate();
                    conn.commit();
                }
            } else if (!prev.get(pa[0])[1].equals(settings)) {
                // посылаем команду на перезагрузку параметров валидации в сервисе
                sendServletCommand("/reloadRefSetting/" + pa[0]);
            }
        }

        System.out.println("Настройки справочников загружены");
    }

    /**
     * Сохранение справочника в специальный json - массив массивов строк справочника
     * Первый элемент - ключ, второй - значение, третий - массив всех полей записи
     * В некоторых справочниках есть дублирование в значениях полей, которые с точки
     * зрения юзеров является ключевым,
     * например в НМУ 1.2.643.5.1.13.13.11.1070 код НМУ дублируется, тк есть
     * аннулированные записи (REL=0). Удаляем их
     */
    @SuppressWarnings("unchecked")
    static private void loadFNSIrefData(String oid, String ver) throws IOException, SQLException {
        PreparedStatement st = conn.prepareStatement(
                "select rowsCount, keyField, valueField from fnsi_ref  where codeSystem=? and version=?");
        st.setString(1, oid);
        st.setString(2, ver);
        ResultSet passport = st.executeQuery();
        passport.next();

        PreparedStatement st2 = conn
                .prepareStatement("select load, keyField, valueField, rekv from ref_setting where codeSystem=?");
        st2.setString(1, oid);
        ResultSet setting = st2.executeQuery();

        String[] rekv = null;
        if (setting.next()) {
            if (setting.getInt(1) == 0) // не загружаем справочник
                return;
            if (setting.getString(4) != null)
                rekv = setting.getString(4).split(",");
        } else {
            setting = null;
        }

        Ref ref = new Ref(oid, ver);

        int count = ((passport.getInt(1) - 1) / FNSI_PORTION) + 1;
        for (int page = 1; page < count + 1; page++) {
            URL url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/data?userKey=" + FNSI_USERKEY + "&identifier="
                    + oid + "&version=" + ver + "&page=" + page + "&size=" + FNSI_PORTION);
            JSONObject data = getFNSIJSON(url, true);

            for (Object _psObject : data.getJSONArray("list")) {
                JSONArray psObject = (JSONArray) _psObject;
                Object[] record = new Object[3];
                record[2] = new LinkedList<String>();
                String REL = "1";
                for (Object _obj : psObject) {
                    JSONObject obj = (JSONObject) _obj;
                    String c = obj.getString("column");
                    String v = (!obj.isNull("value")) ? obj.getString("value") : null;
                    if (c.equals("REL"))
                        REL = v;
                    if (setting != null && c.equals(setting.getString(2))) {
                        record[0] = v;
                    } else if (c.equals(passport.getString(2))) {
                        record[0] = v;
                    } else {
                        if (rekv != null && Arrays.asList(rekv).indexOf(c) >= 0) {
                            ((LinkedList<String>) record[2]).add(v);
                        } else if (setting != null && c.equals(setting.getString(3))) {
                            record[1] = v;
                        } else if (c.equals(passport.getString(3))) {
                            record[1] = v;
                        }
                    }
                }

                if ("1".equals(REL)) { // REL - отработка случая НМУ
                    ref.addRecord(record);
                }
            }
        }
        if (!ref.isEmpty()) {
            ref.saveBinary(VALIDATOR_DATA_PATH + "avl/" + ref.codeSystem + "-" + ref.version + ".avl");
        }
    }

    /**
     * если версия справочника задана маской тип '1.*', то надо подобрать нужную
     * версию
     */
    static private String getFNSIref_ver(String oid, String ver) throws IOException, SQLException {
        if (!ver.contains("*") || ver.equals("*")) {
            return ver;
        }

        URL url = new URL(
                "https://nsi.rosminzdrav.ru:443/port/rest/versions?userKey=" + FNSI_USERKEY + "&identifier=" + oid);
        JSONObject json = getFNSIJSON(url, false);

        if (!"OK".equals(json.get("result"))) {
            throw new IOException("ФНСИ вернул не ОК - " + json.get("result"));
        }

        String v = ver.split("\\.")[0];
        for (Object _r : json.getJSONArray("list")) {
            JSONObject r = (JSONObject) _r;
            if (r.getString("version").split("\\.")[0].equals(v))
                return r.getString("version");
        }

        System.out.println("Not found: " + oid + "-" + ver);
        return "*";
    }

    static private void loadFNSIrefPassport(String oid, String ver) throws IOException, SQLException {
        String ver2 = getFNSIref_ver(oid, ver);
        String ver_param = null;

        if (!ver2.equals("*")) {
            ver_param = ver2;

            PreparedStatement st = conn
                    .prepareStatement("select actual from fnsi_ref where codeSystem=? and version=?");
            st.setString(1, oid);
            st.setString(2, ver2);
            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                if (rs.getString(1) == null) {
                    PreparedStatement st2 = conn
                            .prepareStatement("update fnsi_ref set actual=? where codeSystem=? and version=?");
                    st2.setString(1, ver);
                    st2.setString(2, oid);
                    st2.setString(3, ver2);
                    st2.executeUpdate();
                    st2.close();
                }
                return;
            }
        }

        if (downloaded.contains(oid + ((ver_param != null) ? "&version=" + ver_param : ""))) {
            return;
        } else {
            downloaded.add(oid + ((ver_param != null) ? "&version=" + ver_param : ""));
        }

        URL url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/passport?userKey=" + FNSI_USERKEY + "&identifier="
                + oid + ((ver_param != null) ? "&version=" + ver_param : ""));
        JSONObject passport = getFNSIJSON(url, false);

        if (!"OK".equals(passport.get("result"))) {
            if (passport.get("resultText").equals("Запрашиваемая версия не существует")) {
                return;
            } else {
                throw new IOException("ФНСИ вернул не ОК - " + passport.get("result"));
            }
        }

        {
            PreparedStatement st = conn.prepareStatement("select * from fnsi_ref where codeSystem=? and version=?");
            st.setString(1, passport.getString("oid"));
            st.setString(2, passport.getString("version"));
            ResultSet rs = st.executeQuery();

            if (rs.next()) {
                PreparedStatement st2 = conn
                        .prepareStatement("update fnsi_ref set actual=? where codeSystem=? and version=?");
                st2.setString(1, ver);
                st2.setString(2, passport.getString("oid"));
                st2.setString(3, passport.getString("version"));
                st2.executeUpdate();
                return;
            }
        }

        LinkedList<String> others = new LinkedList<String>();
        for (Object _r : passport.getJSONArray("codes")) {
            JSONObject r = (JSONObject) _r;
            if ("TYPE_OTHER".equals(r.get("type")))
                others.add(r.getString("value"));
        }

        String key = "";
        String value = "";
        for (Object _k : passport.getJSONArray("keys")) {
            JSONObject k = (JSONObject) _k;
            if (key.equals("") && "PRIMARY".equals(k.get("type"))) {
                key = k.getString("field");
            } else if (value.equals("") && "VALUE".equals(k.get("type"))) {
                value = k.getString("field");
            }
            if (!key.equals("") && !value.equals(""))
                break;
        }

        LinkedList<String> fields = new LinkedList<String>();
        for (Object _r : passport.getJSONArray("fields")) {
            JSONObject r = (JSONObject) _r;
            if (!key.equals(r.get("field")) && !value.equals(r.get("field")))
                fields.add(r.getString("field"));
        }

        PreparedStatement st2 = conn.prepareStatement("INSERT INTO fnsi_ref VALUES (?,?,?,?,?,?,?,?,?)");
        st2.setString(1, passport.getString("oid"));
        st2.setString(2, passport.getString("version"));
        st2.setString(3, passport.getString("fullName"));
        st2.setInt(4, passport.getInt("rowsCount"));
        st2.setString(5, String.join(",", others));
        st2.setString(6, key);
        st2.setString(7, value);
        st2.setString(8, String.join(",", fields));
        st2.setString(9, ver);
        st2.executeUpdate();
        st2.close();

        loadFNSIrefData(passport.getString("oid"), passport.getString("version"));

        // посылаем команду на сброс сведений о последних версиях справочников в сервис
        // валидации
        sendServletCommand("/reloadRefSetting/" + oid);
    }

    static private void loadFNSIref_semd_refs() throws IOException, SQLException {
        String ver = null;
        {
            PreparedStatement st = conn
                    .prepareStatement("select version from fnsi_ref_ver where oid='1.2.643.5.1.13.13.99.2.805'");
            ResultSet rs = st.executeQuery();
            if (rs.next()) {
                ver = rs.getString(1);
            }
            rs.close();
            st.close();
        }

        URL url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/passport?userKey=" + FNSI_USERKEY
                + "&identifier=1.2.643.5.1.13.13.99.2.805");
        JSONObject passport = getFNSIJSON(url, false);

        if (ver != null && ver.equals(passport.get("version"))) {
            System.out.println("Справочник 'Реестр справочников СЭМД' актуальный");
            return;
        }

        {
            PreparedStatement st = conn.prepareStatement("delete from fnsi_semd_refs");
            st.executeUpdate();
            st.close();
        }

        HashMap<String, Integer> params = new HashMap<String, Integer>();
        params.put("ID", 0);
        params.put("OID_IG", 1);
        params.put("OID", 2);
        params.put("NAME_S", 4);
        params.put("VERSION", 3);

        PreparedStatement st2 = conn
                .prepareStatement("INSERT INTO fnsi_semd_refs VALUES (" + "?,".repeat(params.size() - 1) + "?" + ")");

        int c = ((passport.getInt("rowsCount") - 1) / FNSI_PORTION) + 1;
        for (int page = 1; page < c + 1; page++) {
            url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/data?userKey=" + FNSI_USERKEY
                    + "&identifier=1.2.643.5.1.13.13.99.2.805&page=" + page + "&size=" + FNSI_PORTION);
            JSONObject data = getFNSIJSON(url, true);

            for (Object _psObject : data.getJSONArray("list")) {
                JSONArray psObject = (JSONArray) _psObject;
                Object[] pa = new Object[params.size()];

                for (Object _obj : psObject) {
                    JSONObject obj = (JSONObject) _obj;
                    String column = obj.getString("column").toUpperCase();
                    if (params.containsKey(column) && !obj.isNull("value")) {
                        int _i = params.get(column);
                        pa[_i] = obj.get("value");
                    }
                }
                for (int i = 0; i < pa.length; i++) {
                    st2.setObject(i + 1, pa[i]);
                }
                st2.executeUpdate();
            }
        }
        st2.close();

        {
            PreparedStatement st = conn
                    .prepareStatement("update fnsi_ref_ver set version=? where oid='1.2.643.5.1.13.13.99.2.805'");
            st.setString(1, passport.getString("version"));
            st.executeUpdate();
            st.close();
        }
        conn.commit();

        // посылаем команду на перезагрузку данных справочника
        // 1.2.643.5.1.13.13.99.2.805 в сервис валидации СЭМД
        sendServletCommand("/reloadFNSIEMD");

        System.out.println("Справочник 'Реестр справочников СЭМД' обновлен до версии " + passport.get("version"));
    }

    static private void loadFNSIref_emd() throws IOException, SQLException {
        String ver = null;
        {
            PreparedStatement st = conn
                    .prepareStatement("select version from fnsi_ref_ver where oid='1.2.643.5.1.13.13.11.1520'");
            ResultSet rs = st.executeQuery();
            if (rs.next()) {
                ver = rs.getString(1);
            }
            rs.close();
            st.close();
        }

        URL url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/passport?userKey=" + FNSI_USERKEY
                + "&identifier=1.2.643.5.1.13.13.11.1520");
        JSONObject passport = getFNSIJSON(url, false);

        if (ver != null && ver.equals(passport.get("version"))) {
            System.out.println("Справочник типов РЭМД актуальный");
            return;
        }

        {
            PreparedStatement st = conn.prepareStatement("delete from fnsi_emd");
            st.executeUpdate();
            st.close();
        }

        HashMap<String, Integer> params = new HashMap<String, Integer>();
        params.put("ID", 0);
        params.put("TYPE", 1);
        params.put("NAME", 2);
        params.put("START_DATE", 3);
        params.put("END_DATE", 4);
        params.put("IMPLEMENTATION_GUIDE", 5);
        params.put("GIT_LINK", 6);

        PreparedStatement st2 = conn
                .prepareStatement("INSERT INTO fnsi_emd VALUES (" + "?,".repeat(params.size() - 1) + "?" + ")");

        int c = ((passport.getInt("rowsCount") - 1) / FNSI_PORTION) + 1;
        for (int page = 1; page < c + 1; page++) {
            url = new URL("https://nsi.rosminzdrav.ru:443/port/rest/data?userKey=" + FNSI_USERKEY
                    + "&identifier=1.2.643.5.1.13.13.11.1520&page=" + page + "&size=" + FNSI_PORTION);
            JSONObject data = getFNSIJSON(url, true);

            for (Object _psObject : data.getJSONArray("list")) {
                JSONArray psObject = (JSONArray) _psObject;
                Object[] pa = new Object[params.size()];
                for (Object _obj : psObject) {
                    JSONObject obj = (JSONObject) _obj;
                    String column = obj.getString("column").toUpperCase();
                    if (params.containsKey(column) && !obj.isNull("value")) {
                        int _i = params.get(column);
                        pa[_i] = obj.get("value");
                    }
                }
                for (int i = 0; i < pa.length; i++) {
                    st2.setObject(i + 1, pa[i]);
                }
                st2.executeUpdate();
            }
        }
        st2.close();

        {
            PreparedStatement st = conn
                    .prepareStatement("update fnsi_ref_ver set version=? where oid='1.2.643.5.1.13.13.11.1520'");
            st.setString(1, passport.getString("version"));
            st.executeUpdate();
            st.close();
        }
        conn.commit();

        System.out.println("Справочник типов РЭМД обновлен до версии " + passport.get("version"));
    }

    public static void main(String[] args) {
        for (String param : args) {
            String[] p = param.split("=");
            if (p.length != 2) {
                System.err.println("Ошибочный формат параметра - '" + param + "'");
                return;
            }
            if (p[0].equals("DB")) {
                SQLITE3_CONNECTION = "jdbc:sqlite:"+p[1];
            } else if (p[0].equals("DATA_PATH")) {
                VALIDATOR_DATA_PATH = p[1];
            } else if (p[0].equals("URL")) {
                VALIDATOR_URL = p[1];
            } else if (p[0].equals("USERKEY")) {
                FNSI_USERKEY = p[1];
            }
        }
        if (SQLITE3_CONNECTION == null || SQLITE3_CONNECTION == null || FNSI_USERKEY == null) {
            System.out.println("Не приведены параметры. Пример: 'DB=/home/user/database DATA_PATH=/home/user/ URL=http://infomed.med.1c.ru/a/semd USERKEY=xxx-xxx-xxx-xxx'");
            return;
        }

        try {
            conn = DriverManager.getConnection(SQLITE3_CONNECTION);
            conn.setAutoCommit(false);
        
            loadFNSIlist_json();
            loadFNSIref_emd();
            loadFNSIref_semd_refs();
        
            // обновляем справочники
            PreparedStatement st = conn.prepareStatement("update fnsi_ref set actual=null where actual like '%*%'");
            st.executeUpdate();
            st.close();
            conn.commit();
        
            // определяем необходимость загрузки справочника. Если справочник не используется в регистрируемых СЭМД, то не загружаем
            String sql = String.join(" ", 
                "select distinct ref, version",
                "from fnsi_semd_refs",
                "inner join fnsi_emd on fnsi_emd.git_link=fnsi_semd_refs.oid",
                "where fnsi_emd.end_date is null or substr(fnsi_emd.end_date, 7)||substr(fnsi_emd.end_date,4,2)||substr(fnsi_emd.end_date,1,2) >= ");
            sql += "'" + new SimpleDateFormat("yyyyMMdd").format(Calendar.getInstance().getTime()) +"'";
            st = conn.prepareStatement(sql);
            ResultSet rs = st.executeQuery();
            while (rs.next()) {
                loadFNSIrefPassport(rs.getString(1), rs.getString(2));
                conn.commit();
            }
            rs.close();
            st.close();
            // конец обновления справочников
        
            // удаляем версии справочников, которые раньше были актуальными, а теперь на них нет ссылок
            st = conn.prepareStatement(String.join(" ",
                "select fnsi_ref.codeSystem, fnsi_ref.version, rowsCount",
                "from fnsi_ref",
                "left join ref_setting on ref_setting.codeSystem=fnsi_ref.codeSystem",
                "where actual is null and (load is null or load=1)"));
            rs = st.executeQuery();
            while (rs.next()) {
                int c = ((rs.getInt(3)-1)/FNSI_PORTION)+1;
                for (int page = 1; page < c+1; page++) {
                    File file = new File(VALIDATOR_DATA_PATH + "/fnsi/" + rs.getString(1) + "-" + rs.getString(2) + "-page" + Integer.toString(page));
                    file.delete();
                }
        
                File avl_file = new File(VALIDATOR_DATA_PATH + "/avl/" + rs.getString(1) + "-" + rs.getString(2) + ".avl");
                avl_file.delete();
            }
            rs.close();
            st.close();

            st = conn.prepareStatement("delete from fnsi_ref where actual is null");
            st.executeUpdate();
            conn.commit();
            // конец удаления
        
            conn.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
            ex.printStackTrace();
        }
    }
}